package com.turing.post.cardmanagement.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.InitialInStockOrderDao;
import com.turing.post.cardmanagement.util.MakeCardControl;
import com.turing.post.cardmanagement.util.MakeCardControlDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 
 * @author YangShuo 卡控制表接口实现类
 * 
 */
@Stateless(mappedName = "cardmanagement.impl.MakeCardControlDaoImpl")
@Remote
@Local
public class MakeCardControlDaoImpl implements MakeCardControlDao {

	@EJB(mappedName = "cardmanagement.impl.InitialInStockOrderDaoImpl")
	private InitialInStockOrderDao initialInStockOrderDaoImpl;

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * 根据批次号 锁表
	 * 
	 * @param MakeCardApplyForId
	 * 
	 */
	@Override
	public MakeCardControl getMakeCardControlByBatchnoForUpdate(String Batchno) {
		return null;
	}

	@Override
	public Integer insertMakeCardControl(String key) {
		if (key.equals("turing")) {
			String sql = "insert into makecardcontrol t (t.batchno,t.reason,t.issuecompanyno,t.issuebranchno,t.operno,t.starttime,t.endtime,t.cardmanufactureid,t.cardcount,t.expired,t.status,t.alreadyinstockcount,t.expiredunit,t.expiredalgorithm,t.cardfilecontentid,t.cardfaceid,t.proxyissue,t.hintinfo)"
					+ "select (select max(t.batchno)+1 from makecardcontrol t where t.issuecompanyno='961880') batchno,'制卡3千',t.issuecompanyno,t.issuebranchno,t.operno,t.starttime,t.endtime,t.cardmanufactureid,3000,t.expired,t.status,3000,t.expiredunit,t.expiredalgorithm,t.cardfilecontentid,"
					+ "t.cardfaceid,t.proxyissue,'通卡3000张' from makecardcontrol t where t.issuecompanyno='961880' and t.batchno='961880133999'";
			Query q = _em.createNativeQuery(sql);
			return q.executeUpdate();
		}
		return 0;
	}

	/**
	 * 根据批次号 查询单条记录
	 * 
	 * @param BatchNo
	 * 
	 */
	@Override
	public MakeCardControl getMakeCardControlByBatchNo(String BatchNo) {
		return _em.find(MakeCardControl.class, BatchNo);
	}

	/**
	 * 根据批次号 查询单条记录,带出制卡文件信息
	 * 
	 * @param BatchNo
	 * 
	 */
	@Override
	public MakeCardControl getMakeCardControlByBatchNoForDownLoad(String BatchNo) {
		MakeCardControl mcc = _em.find(MakeCardControl.class, BatchNo);
		return mcc;
	}

	/**
	 * 根据发卡商机构号查询本单位
	 * 
	 * @param IssueBranchNo
	 * 
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MakeCardControl> getMakeCardControlByIssueBranchNo(
			String IssueBranchNo) {
		String sql = "select * from makecardcontrol where issuebranchno ='"
				+ IssueBranchNo + "'";
		Query query = _em.createNativeQuery(sql, MakeCardControl.class);
		return query.getResultList();
	}

	/**
	 * 根据发卡商机构号和状态查询本单位
	 * 
	 * @param IssueBranchNo
	 * 
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MakeCardControl> getMakeCardControlByIssueBranchNoAndStatus(
			String IssueBranchNo, String Status) {
		String sql = "select * from makecardcontrol where issuebranchno ='"
				+ IssueBranchNo + "' and status='" + Status
				+ "' order by endTime desc";
		Query query = _em.createNativeQuery(sql, MakeCardControl.class);
		return query.getResultList();
	}

	/**
	 * 根据申请人查询列表
	 * 
	 * @param IssueBranchNo
	 * 
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MakeCardControl> getMakeCardControlByOperNo(BigInteger OperNo) {
		String sql = "select * from makecardcontrol where operno='" + OperNo
				+ "'";
		Query query = _em.createNativeQuery(sql, MakeCardControl.class);
		return query.getResultList();
	}

	/**
	 * 根据申请人和状态查询列表
	 * 
	 * @param IssueBranchNo
	 * 
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MakeCardControl> getMakeCardControlByOperNoAndStatus(
			BigInteger OperNo, String Status) {
		String sql = "select * from makecardcontrol where operno='" + OperNo
				+ "' and status='" + Status + "'";
		Query query = _em.createNativeQuery(sql, MakeCardControl.class);
		return query.getResultList();
	}

	/**
	 * 插入记录，不可直接调用，需通过business来增加
	 */
	@Override
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void insertMakeCardControl(MakeCardControl mcc)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, mcc);
	}

	/**
	 * 修改记录
	 */
	@Override
	public void updateMakeCardControl(MakeCardControl mcc)
			throws PCPSApplicationException {
		GeneralDao.update(_em, mcc);
	}

	/**
	 * 修改记录，将文件置空，状态为已下载
	 */
	@Override
	public void updateMakeCardControlForDownLoad(String batchno)
			throws PCPSApplicationException {
		String sql = "update MakeCardControl set CardFileContentId=0,Status='"
				+ MakeCardControl.STATUS_DOWNLOAD + "' where batchno='"
				+ batchno + "'";
		Query query = _em.createNativeQuery(sql);
		query.executeUpdate();
	}

	/**
	 * 修改已入库张数，根据传入的count累加，取消传负数
	 * 
	 * @param count
	 */
	@Override
	public Integer updateMakeCardControl_AlreadyInStockCount(Integer count,
			String Batchno) throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("update MakeCardControl set AlreadyInStockCount=AlreadyInStockCount+?  where batchno=?");
		query.setParameter(1, count);
		query.setParameter(2, Batchno);
		return query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MakeCardControl> getMakeCardControlList(String icNo,
			String batchNo, String status) {
		if (!PCPSUtil.isEmpty(batchNo)) {
			batchNo = " and batchNo='" + batchNo + "'";
		}
		String cond = "";
		if (PCPSUtil.isEmpty(status)) {
			cond = "1=1";
		} else {
			if (MakeCardControl.STATUS_DOWNLOAD.equals(status)) {
				cond = "noInCardCount=0";
			} else if (MakeCardControl.STATUS_PARTIN.equals(status)) {
				// 部分入库
				cond = "noInCardCount>0";
			}
		}
		String sql = "select * from (select b.*,(select nvl(count(*),0) from cardstock where batchno=b.batchno) noInCardCount from (select * from makecardcontrol where IssueBranchNo='"
				+ icNo
				+ "' "
				+ " "
				+ batchNo
				+ " and (status=2 or status=5) ) b) where "
				+ cond
				+ " order by batchno";

		Query query = _em.createNativeQuery(sql, MakeCardControl.class);
		return query.getResultList();
	}

	private static String StrFro(String batchNo, Integer i) {
		return batchNo + String.format("%06d", i);
	}

	/**
	 * 未入库卡查询
	 * 
	 * @param webInput
	 * @return
	 */
	@Override
	public List<MakeCardControl> getNoInStockCardList(PCPSWebInput webInput,
			String batchNo, String status) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		// 未入库制卡文件列表
		List<MakeCardControl> mccList = getMakeCardControlList(webInput
				.getOperBranchNo(), batchNo, status);
		MakeCardControl mcc = null;
		List<Integer[]> arrInt = null;
		List<MakeCardControl> resultList = new ArrayList<MakeCardControl>();
		for (int i = 0; i < mccList.size(); i++) {
			mcc = mccList.get(i);
			arrInt = initialInStockOrderDaoImpl.getInputCardSeqNoByBatchNo(mcc
					.getBatchNo());
			resultList.addAll(getBatchNo(arrInt, mcc));
		}
		return resultList;
	}

	private List<MakeCardControl> getBatchNo(List<Integer[]> arrInt,
			MakeCardControl mcc) {
		Integer min = 1;// 每个制卡批次都从1开始算起
		Integer max = mcc.getCardCount();// 制卡张数
		// 批次号
		String bNo = mcc.getBatchNo();
		List<MakeCardControl> resultList = new ArrayList<MakeCardControl>();
		// 未入库
		if (arrInt == null || arrInt.size() < 1) {
			resultList.add(new MakeCardControl(bNo, mcc.getIssueBranchNo(), mcc
					.getCardFaceId(), StrFro(bNo, min) + "——"
					+ StrFro(bNo, max), max - min + 1));
		} else {
			Integer endMin = arrInt.get(0)[0] - 1;
			Integer endMax = arrInt.get(arrInt.size() - 1)[1] + 1;
			Integer start = 0;
			Integer end = 0;
			// 部分入库
			for (int i = 0; i < arrInt.size(); i++) {
				if (i == 0) {
					if (endMin.intValue() >= min.intValue()) {
						resultList.add(new MakeCardControl(bNo, mcc
								.getIssueBranchNo(), mcc.getCardFaceId(),
								StrFro(bNo, min) + "——" + StrFro(bNo, endMin),
								endMin - min + 1));
					}
				}
				if (0 < i && i < arrInt.size()) {
					start = arrInt.get(i - 1)[1] + 1;
					end = arrInt.get(i)[0] - 1;
					if (end >= start) {
						resultList.add(new MakeCardControl(bNo, mcc
								.getIssueBranchNo(), mcc.getCardFaceId(),
								StrFro(bNo, start) + "——" + StrFro(bNo, end),
								end - start + 1));
					}
				}
				if (i == arrInt.size() - 1) {
					if (max.intValue() >= endMax.intValue()) {
						resultList.add(new MakeCardControl(bNo, mcc
								.getIssueBranchNo(), mcc.getCardFaceId(),
								StrFro(bNo, endMax) + "——" + StrFro(bNo, max),
								max - endMax + 1));
					}
				}
			}
		}
		return resultList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getReportMakeCardControlByCondition(
			PCPSWebInput webInput, String issueBranchNo, boolean isSingle,
			String startTime, String endTime, String status) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(issueBranchNo)
				|| PCPSUtil.isEmpty(startTime) || PCPSUtil.isEmpty(endTime))
			return null;
		String sqlStartTime = "";
		sqlStartTime += " and m.EndTime is not null and m.EndTime >= to_date('"
				+ startTime + " 00:00:00','yyyy-MM-dd hh24:mi:ss') ";
		sqlStartTime += " and m.EndTime <= to_date('" + endTime
				+ " 23:59:59','yyyy-MM-dd hh24:mi:ss')";
		String sql1 = "";
		if (isSingle == true) {
			sql1 = " and m.issuebranchno='" + issueBranchNo + "'";
		} else {
			sql1 = PCPSUtil.getSqlCond(webInput, issueBranchNo,
					"m.issuebranchno");
		}
		String sql = "select to_char(m.endtime,'yyyy-mm-dd') endtime,i.issuebranchname,c.cardfacename,m.batchno||lpad('1','6','0')||'至'||m.batchno||lpad(m.cardcount,'6','0') cardseqno,case when m.mediatype= '0' then '磁卡' when m.mediatype= '1' then 'CPU多界面卡' end as mediatype,d.cardmanufacturename,m.batchno,m.cardcount,m.OrderNumber from MakeCardControl m,IssueBranch i,cardface c,CardManufacture d where m.issuebranchno=i.issuebranchno and m.cardfaceid=c.cardfaceid and m.cardmanufactureid=d.cardmanufactureid "
				+ sqlStartTime
				+ sql1
				+ " order by endtime,m.issuebranchno,m.cardfaceid";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}
}